<!doctype html>
<html lang="zh-CN">
<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>oracle语法总结 | Jeremy Sze的博客</title>
    <meta property="og:title" content="oracle语法总结 - Jeremy Sze的博客">
    <meta property="og:type" content="article">
        
    <meta property="article:published_time" content='2020-10-02T10:30:28&#43;08:00'>
        
        
    <meta property="article:modified_time" content='2020-10-02T10:30:28&#43;08:00'>
        
    <meta name="Keywords" content="java,博客">
    <meta name="description" content="oracle语法总结">
        
    <meta name="author" content="Jeremy Sze">
    <meta property="og:url" content="https://laoer123.gitee.io/laoer123/post/Oracle%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93/">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">

    <link rel="stylesheet" href='/laoer123/css/normalize.css'>
    <link rel="stylesheet" href='/laoer123/css/style.css'>
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    
    <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <script>
    (adsbygoogle = window.adsbygoogle || []).push({
        google_ad_client: "ca-pub-4031353640611810",
        enable_page_level_ads: true
    });
    </script>
    


    
    
        <link rel="stylesheet" href='/laoer123/css/douban.css'>
    
        <link rel="stylesheet" href='/laoer123/css/other.css'>
    
</head>


<body>
    <header id="header" class="clearfix">
    <div class="container">
        <div class="col-group">
            <div class="site-name ">
                
                    <a id="logo" href="https://laoer123.gitee.io/laoer123">
                        Jeremy Sze的博客
                    </a>
                
                <p class="description">专注于Java、JavaScript、软件开发、web开发、分布式，微服务系统开发</p>
            </div>
            <div>
                <nav id="nav-menu" class="clearfix">
                    <a class="current" href="https://laoer123.gitee.io/laoer123">首页</a>
                    
                    <a  href="https://laoer123.gitee.io/laoer123/about/" title="关于">关于</a>
                    
                </nav>
            </div>
        </div>
    </div>
</header>

    <div id="body">
        <div class="container">
            <div class="col-group">

                <div class="col-8" id="main">
                    
<div class="res-cons">
    <style type="text/css">
    .post-toc {
        position: fixed;
        width: 200px;
        margin-left: -210px;
        padding: 5px 10px;
        font-family: Athelas, STHeiti, Microsoft Yahei, serif;
        font-size: 12px;
        border: 1px solid rgba(0, 0, 0, .07);
        border-radius: 5px;
        background-color: rgba(255, 255, 255, 0.98);
        background-clip: padding-box;
        -webkit-box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        word-wrap: break-word;
        white-space: nowrap;
        -webkit-box-sizing: border-box;
        box-sizing: border-box;
        z-index: 999;
        cursor: pointer;
        max-height: 70%;
        overflow-y: auto;
        overflow-x: hidden;
    }

    .post-toc .post-toc-title {
        width: 100%;
        margin: 0 auto;
        font-size: 20px;
        font-weight: 400;
        text-transform: uppercase;
        text-align: center;
    }

    .post-toc .post-toc-content {
        font-size: 15px;
    }

    .post-toc .post-toc-content>nav>ul {
        margin: 10px 0;
    }

    .post-toc .post-toc-content ul {
        padding-left: 20px;
        list-style: square;
        margin: 0.5em;
        line-height: 1.8em;
    }

    .post-toc .post-toc-content ul ul {
        padding-left: 15px;
        display: none;
    }

    @media print,
    screen and (max-width:1057px) {
        .post-toc {
            display: none;
        }
    }
</style>
<div class="post-toc" style="position: absolute; top: 188px;">
    <h2 class="post-toc-title">文章目录</h2>
    <div class="post-toc-content">
        <nav id="TableOfContents">
  <ul>
    <li><a href="#值得注意的语句">值得注意的语句</a>
      <ul>
        <li><a href="#授权语句">授权语句</a></li>
        <li><a href="#创建序列">创建序列</a></li>
        <li><a href="#视图">视图</a></li>
        <li><a href="#merge-into函数用法">MERGE INTO函数用法</a></li>
        <li><a href="#nvl函数">nvl函数</a></li>
        <li><a href="#exists">exists</a></li>
        <li><a href="#not-exists">not exists</a></li>
        <li><a href="#sys_guid">sys_guid()</a></li>
        <li><a href="#upper">upper()</a></li>
        <li><a href="#lower">lower()</a></li>
        <li><a href="#四舍五入函数round">四舍五入函数：ROUND()</a></li>
        <li><a href="#decode-函数">Decode 函数</a></li>
        <li><a href="#case-when">case when</a></li>
        <li><a href="#索引">索引</a></li>
        <li><a href="#trunc函数的用法">TRUNC函数的用法</a></li>
        <li><a href="#instr函数的格式--俗称字符查找函数">instr()函数的格式  （俗称：字符查找函数）</a></li>
      </ul>
    </li>
    <li><a href="#rownum-用法">rownum 用法</a></li>
    <li><a href="#oracle中的rowid">Oracle中的rowid</a></li>
    <li><a href="#for-update">for update</a>
      <ul>
        <li><a href="#三什么时候需要使用for-update">三、什么时候需要使用for update？</a></li>
        <li><a href="#四for-update悲观锁">四、for update悲观锁</a></li>
      </ul>
    </li>
  </ul>
</nav>
    </div>
</div>
<script type="text/javascript">
    $(document).ready(function () {
        var postToc = $(".post-toc");
        if (postToc.length) {
            var leftPos = $("#main").offset().left;
            if(leftPos<220){
                postToc.css({"width":leftPos-10,"margin-left":(0-leftPos)})
            }

            var t = postToc.offset().top - 20,
                a = {
                    start: {
                        position: "absolute",
                        top: t
                    },
                    process: {
                        position: "fixed",
                        top: 20
                    },
                };
            $(window).scroll(function () {
                var e = $(window).scrollTop();
                e < t ? postToc.css(a.start) : postToc.css(a.process)
            })
        }
    })
</script>
    <article class="post">
        <header>
            <h1 class="post-title">oracle语法总结</h1>
        </header>
        <date class="post-meta meta-date">
            2020年10月2日
        </date>
        
        <div class="post-meta">
            <span>|</span>
            
            <span class="meta-category"><a href='https://laoer123.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93'>数据库</a></span>
            
        </div>
        
        
        <div class="post-meta">
            <span id="busuanzi_container_page_pv">|<span id="busuanzi_value_page_pv"></span><span>
                    阅读</span></span>
        </div>
        
        
        <div class="post-content">
            <h2 id="值得注意的语句">值得注意的语句</h2>
<h3 id="授权语句">授权语句</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">#</span><span style="color:#000;font-weight:bold">Grant</span><span style="color:#000;font-weight:bold">/</span><span style="color:#000;font-weight:bold">Revoke</span> <span style="color:#000;font-weight:bold">object</span> <span style="color:#000;font-weight:bold">privileges</span>    <span style="color:#a61717;background-color:#e3d2d2">表授权</span>  <span style="color:#a61717;background-color:#e3d2d2">把</span>jn34授权给website数据库<span style="color:#a61717;background-color:#e3d2d2">，让</span>website拥有对给表的查询<span style="color:#a61717;background-color:#e3d2d2">，插入，更新的权限</span>

<span style="color:#000;font-weight:bold">grant</span> <span style="color:#000;font-weight:bold">select</span>, <span style="color:#000;font-weight:bold">insert</span>, <span style="color:#000;font-weight:bold">update</span> <span style="color:#000;font-weight:bold">on</span> JN34 <span style="color:#000;font-weight:bold">to</span> WEBSITE;
</code></pre></td></tr></table>
</div>
</div><h3 id="创建序列">创建序列</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">7
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">-- Create sequence 
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> sequence SB_JZFW_GRBM
<span style="color:#000;font-weight:bold">minvalue</span> <span style="color:#099">1</span>
<span style="color:#000;font-weight:bold">maxvalue</span> <span style="color:#099">99999999999999999999999999</span>
<span style="color:#000;font-weight:bold">start</span> <span style="color:#000;font-weight:bold">with</span> <span style="color:#099">1</span>
<span style="color:#000;font-weight:bold">increment</span> <span style="color:#000;font-weight:bold">by</span> <span style="color:#099">1</span>
<span style="color:#000;font-weight:bold">cache</span> <span style="color:#099">20</span>;
</code></pre></td></tr></table>
</div>
</div><p>或者</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">7
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">-- Create sequence 
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> sequence seqpersonid
<span style="color:#000;font-weight:bold">maxvalue</span> <span style="color:#099">9999999999999999999999999999</span>
startwith <span style="color:#099">1</span>
<span style="color:#000;font-weight:bold">increment</span> <span style="color:#000;font-weight:bold">by</span> <span style="color:#099">1</span>
<span style="color:#000;font-weight:bold">cache</span> <span style="color:#099">5</span>
<span style="color:#000;font-weight:bold">cycle</span> ;
</code></pre></td></tr></table>
</div>
</div><p>nextval :取得序列的下一个内容<br>
currval :取得序列的当前内容<br>
cycle :从起始值开始递增，自增到最大值之后又归为起始值继续递增
cache ：设置缓存，数据库一次生成多个序列值保留使用，使用完了之后继续创建</p>
<p>select seqpersonid.nextval from dual;<br>
select seqpersonid.currval from dual;<br>
在插入数据时需要自增的主键中可以这样使用<br>
在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。<br>
序列的管理一般使用工具来管理。</p>
<p>$\color{red}{maxvalue最大28个9，9999999999999999999999999999，maxvalue不写默认就是maxvalue NOMAXVALUE,也是有最大值的，27个9}$</p>
<p>序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">insert</span> <span style="color:#000;font-weight:bold">into</span> person <span style="color:#000;font-weight:bold">values</span>(seqpersonid.nextval,<span style="color:#d14">&#39;李四 &#39;</span>,<span style="color:#099">1</span>,to_date(<span style="color:#d14">&#39;2018-8-18&#39;</span>,<span style="color:#d14">&#39;yyyy-MM-dd&#39;</span>),<span style="color:#d14">&#39;广州&#39;</span>);
</code></pre></td></tr></table>
</div>
</div><h3 id="视图">视图</h3>
<p>视图可以封装引用一条sql查询语句进行展现数据,视图是虚拟的表,不是真实存在的表.为数据库真实的表提供多种展现方式供其他用户查看.</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--scott是普通用户,很多权限没有,为了方便我们给dba权限分配给scott
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">grant</span> dba <span style="color:#000;font-weight:bold">to</span> scott;
<span style="color:#998;font-style:italic">------------------------------------视图--------------------------------------
</span><span style="color:#998;font-style:italic">--介绍:用于给不同的用户提供不同的数据(用户A只能看emp表3个字段,用户B只能看emp表5个字段)
</span><span style="color:#998;font-style:italic">--在当前主用户里面创建视图,分配权限给用户A,用户B
</span><span style="color:#998;font-style:italic">--创建视图语法1(非只读):
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> view视图名字as sql查询语句;
<span style="color:#998;font-style:italic">--示例代码
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> <span style="color:#000;font-weight:bold">view</span> view_empA <span style="color:#000;font-weight:bold">as</span>
<span style="color:#000;font-weight:bold">select</span> empno,ename,job <span style="color:#000;font-weight:bold">from</span> emp;
<span style="color:#998;font-style:italic">--查询视图
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> view_empA;
</code></pre></td></tr></table>
</div>
</div><div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">17
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">18
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">19
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">20
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">21
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">22
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">23
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--更新视图数据
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">update</span> view_empA <span style="color:#000;font-weight:bold">set</span> ename<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;SMITH-1&#39;</span><span style="color:#000;font-weight:bold">where</span> empno<span style="color:#000;font-weight:bold">=</span><span style="color:#099">7369</span>;<span style="color:#998;font-style:italic">--更新成功
</span><span style="color:#998;font-style:italic">--查询视图来源表emp
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> emp;
<span style="color:#998;font-style:italic">--为什么更新了视图,也更新了来源表? 因为视图是虚拟表,所有数据来自于原始表emp
</span><span style="color:#998;font-style:italic">--能不能运行其他用户更新视图数据? 答案:不能,所以需要创建只读视图
</span><span style="color:#998;font-style:italic">--创建视图语法2(只读):
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> view视图名字as sql查询语句withreadonly;
<span style="color:#998;font-style:italic">--创建只读视图
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> <span style="color:#000;font-weight:bold">view</span> view_empB <span style="color:#000;font-weight:bold">as</span>
<span style="color:#000;font-weight:bold">select</span> empno,ename,job,deptno <span style="color:#000;font-weight:bold">from</span> emp <span style="color:#000;font-weight:bold">with</span> <span style="color:#000;font-weight:bold">read</span> <span style="color:#000;font-weight:bold">only</span>;
<span style="color:#000;font-weight:bold">update</span> view_empB <span style="color:#000;font-weight:bold">set</span> ename<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;SMITH-2&#39;</span><span style="color:#000;font-weight:bold">where</span> empno<span style="color:#000;font-weight:bold">=</span><span style="color:#099">7369</span>;<span style="color:#998;font-style:italic">--更新失败,因为只读
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#998;font-style:italic">--创建用户
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">user</span> testA identifiedby testA <span style="color:#000;font-weight:bold">default</span> tablespace webiste;
<span style="color:#998;font-style:italic">--给用户授权
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">grant</span> <span style="color:#000;font-weight:bold">connect</span>,resource <span style="color:#000;font-weight:bold">to</span> testA;
<span style="color:#998;font-style:italic">--给视图view_empB查询权限
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">grant</span> <span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">on</span> view_empB <span style="color:#000;font-weight:bold">to</span> testA;

<span style="color:#998;font-style:italic">------------------------使用testA/testA登录-------------------------
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> scott.view_empA; <span style="color:#998;font-style:italic">--不能看,没有权限
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> scott.view_empB; <span style="color:#998;font-style:italic">--能看,有权限
</span></code></pre></td></tr></table>
</div>
</div><h3 id="merge-into函数用法">MERGE INTO函数用法</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--根据一张表的数据来更新另外一张表
</span><span style="color:#998;font-style:italic">--作用:判断Ｂ表和C表是否满足ON中条件，如果满足则用c表去更新A表
</span><span style="color:#998;font-style:italic"></span>
MERGE <span style="color:#000;font-weight:bold">INTO</span> jn02 A
<span style="color:#000;font-weight:bold">USING</span> (<span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span>
         <span style="color:#000;font-weight:bold">from</span> jn02_04 t
        <span style="color:#000;font-weight:bold">where</span> t.bizstatus <span style="color:#000;font-weight:bold">=</span> <span style="color:#d14">&#39;1&#39;</span>
          <span style="color:#000;font-weight:bold">and</span> t.bizno <span style="color:#000;font-weight:bold">=</span> <span style="color:#d14">&#39;201511033420&#39;</span>) <span style="color:#000;font-weight:bold">C</span>
<span style="color:#000;font-weight:bold">ON</span> ( a.bizno<span style="color:#000;font-weight:bold">=</span><span style="color:#000;font-weight:bold">c</span>.biznobt)
<span style="color:#000;font-weight:bold">WHEN</span> MATCHED <span style="color:#000;font-weight:bold">THEN</span>
  <span style="color:#000;font-weight:bold">UPDATE</span> <span style="color:#000;font-weight:bold">SET</span> 
  A.JN24015 <span style="color:#000;font-weight:bold">=</span> <span style="color:#000;font-weight:bold">C</span>.JN24_0403 , A.JN24016 <span style="color:#000;font-weight:bold">=</span> <span style="color:#000;font-weight:bold">C</span>.JN24_0404 , A.JN24018 <span style="color:#000;font-weight:bold">=</span> <span style="color:#000;font-weight:bold">C</span>.JN24_0405
   <span style="color:#000;font-weight:bold">where</span> A.bizstatus <span style="color:#000;font-weight:bold">=</span> <span style="color:#d14">&#39;1&#39;</span>;
</code></pre></td></tr></table>
</div>
</div><h3 id="nvl函数">nvl函数</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--查询多个字段的函数  查询字段一为null，则采用字段二
</span><span style="color:#998;font-style:italic">--如果字段一为NULL，则函数返回字段二，否则返回字段一本身 
</span><span style="color:#998;font-style:italic"></span>nvl(<span style="color:#a61717;background-color:#e3d2d2">字段一</span>,<span style="color:#a61717;background-color:#e3d2d2">字段二</span>);  
</code></pre></td></tr></table>
</div>
</div><h3 id="exists">exists</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">-- exists : 强调的是是否返回结果集，不要求知道返回什么
</span><span style="color:#998;font-style:italic">-- 而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">exists</span>           <span style="color:#a61717;background-color:#e3d2d2">（返回结果集，为真）</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="not-exists">not exists</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">not</span> <span style="color:#000;font-weight:bold">exists</span>       (<span style="color:#a61717;background-color:#e3d2d2">不返回结果集，为真）</span>
</code></pre></td></tr></table>
</div>
</div><h3 id="sys_guid">sys_guid()</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--随机生成一个uuid
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> sys_guid()  <span style="color:#000;font-weight:bold">from</span>  dual;

</code></pre></td></tr></table>
</div>
</div><h3 id="upper">upper()</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--把小写的字符转换成大写的字符
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">upper</span>(<span style="color:#d14">&#39;smith&#39;</span>)  <span style="color:#000;font-weight:bold">from</span>  dual;
</code></pre></td></tr></table>
</div>
</div><h3 id="lower">lower()</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--把大写字符变成小写字符
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">lower</span>(<span style="color:#d14">&#39;SMITH&#39;</span>)  <span style="color:#000;font-weight:bold">from</span>  dual;
</code></pre></td></tr></table>
</div>
</div><h3 id="四舍五入函数round">四舍五入函数：ROUND()</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--默认情况下ROUND四舍五入取整，可以自己指定保留的位数。
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> ROUND(<span style="color:#099">12</span>.<span style="color:#099">534</span>)  <span style="color:#000;font-weight:bold">from</span>  dual;   <span style="color:#998;font-style:italic">--得 13
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> ROUND(<span style="color:#099">12</span>.<span style="color:#099">534</span>,<span style="color:#099">2</span>)  <span style="color:#000;font-weight:bold">from</span>  dual;   <span style="color:#998;font-style:italic">--  保留小数点后两位 得 12.53
</span></code></pre></td></tr></table>
</div>
</div><h3 id="decode-函数">Decode 函数</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--if-else判断函数decode函数,格式:
</span><span style="color:#998;font-style:italic">-- decode(数据,&#39;aa&#39;,&#39;数据为aa的时候返回的值&#39;,&#39;bb&#39;,&#39;数据为bb的时候返回的值&#39;,...,&#39;else之前条件都不符合返回的值&#39;)
</span><span style="color:#998;font-style:italic">-- 显示员工职位中文名字
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> emp.ename,emp.job, 
decode(
	emp.job,
	<span style="color:#d14">&#39;CLERK&#39;</span>,<span style="color:#d14">&#39;业务员&#39;</span>,
	<span style="color:#d14">&#39;SALESMAN&#39;</span>,<span style="color:#d14">&#39;销售员&#39;</span>,
	<span style="color:#d14">&#39;MANAGER&#39;</span>,<span style="color:#d14">&#39;经理&#39;</span>,
	<span style="color:#d14">&#39;PRESIDENT&#39;</span>,<span style="color:#d14">&#39;总裁&#39;</span>,
	<span style="color:#d14">&#39;其他&#39;</span>
       ) <span style="color:#a61717;background-color:#e3d2d2">职位</span>
<span style="color:#000;font-weight:bold">from</span> emp;
</code></pre></td></tr></table>
</div>
</div><h3 id="case-when">case when</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">Select</span> 
	emp.empno,
	emp.ename,
	<span style="color:#000;font-weight:bold">case</span> emp.job
	<span style="color:#000;font-weight:bold">when</span>	<span style="color:#d14">&#39;CLERK&#39;</span> <span style="color:#000;font-weight:bold">then</span><span style="color:#d14">&#39; 业务员&#39;</span>
	<span style="color:#000;font-weight:bold">when</span>	<span style="color:#d14">&#39;SALESMAN&#39;</span> <span style="color:#000;font-weight:bold">then</span> <span style="color:#d14">&#39;销售&#39;</span>
	<span style="color:#000;font-weight:bold">when</span>	<span style="color:#d14">&#39;MANAGER&#39;</span> <span style="color:#000;font-weight:bold">then</span> <span style="color:#d14">&#39;经理&#39;</span>
	<span style="color:#000;font-weight:bold">when</span>	<span style="color:#d14">&#39;ANALYST&#39;</span> <span style="color:#000;font-weight:bold">then</span> <span style="color:#d14">&#39;分析员&#39;</span>
	<span style="color:#000;font-weight:bold">when</span>	<span style="color:#d14">&#39;PRESIDENT&#39;</span> <span style="color:#000;font-weight:bold">then</span> <span style="color:#d14">&#39;总裁&#39;</span>
	<span style="color:#000;font-weight:bold">else</span>	<span style="color:#d14">&#39;无业&#39;</span>
	<span style="color:#000;font-weight:bold">end</span> <span style="color:#a61717;background-color:#e3d2d2">职位</span> 
	<span style="color:#000;font-weight:bold">from</span> emp;
</code></pre></td></tr></table>
</div>
</div><h3 id="索引">索引</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">17
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">18
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">19
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">20
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">21
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">22
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">23
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">24
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">25
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">26
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">27
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">28
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">29
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--测试索引查询性能.数据量一定非常大,才能有效果
</span><span style="color:#998;font-style:italic"></span>createtable t_test(
idnumber(<span style="color:#099">10</span>),
namevarchar2(<span style="color:#099">50</span>)
);

<span style="color:#998;font-style:italic">--创建序列
</span><span style="color:#998;font-style:italic"></span>createsequence seq_test;

<span style="color:#998;font-style:italic">--插入测试数据5000000条
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">for</span> i in1..<span style="color:#099">5000000</span>
loop
insertinto t_test <span style="color:#000;font-weight:bold">values</span>(seq_test.nextval,<span style="color:#d14">&#39;测试数据&#39;</span><span style="color:#000;font-weight:bold">||</span>i);
endloop;
<span style="color:#000;font-weight:bold">commit</span>;
<span style="color:#000;font-weight:bold">end</span>;

<span style="color:#998;font-style:italic">--查询数据表总记录数
</span><span style="color:#998;font-style:italic"></span>selectcount(<span style="color:#000;font-weight:bold">*</span>) <span style="color:#000;font-weight:bold">from</span> t_test;
<span style="color:#998;font-style:italic">--没有建索引进行查询
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> t_test wherename<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;测试数据4123456&#39;</span>;<span style="color:#998;font-style:italic">--0.859
</span><span style="color:#998;font-style:italic">--创建索引
</span><span style="color:#998;font-style:italic"></span>createindex seq_test_name <span style="color:#000;font-weight:bold">on</span> t_test(name);
<span style="color:#998;font-style:italic">--使用索引查询
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> t_test wherename<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;测试数据4523456&#39;</span>;<span style="color:#998;font-style:italic">--0.047
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#998;font-style:italic">--总结:索引可以提高查询性能,但是不能乱建索引,因为索引会占用空间与资源
</span><span style="color:#998;font-style:italic">--       经常查询的字段,建议创建索引
</span></code></pre></td></tr></table>
</div>
</div><h3 id="trunc函数的用法">TRUNC函数的用法</h3>
<p>TRUNC函数用于对值进行截断。<br>
用法有两种：TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期。<br>
(1)截断数字：<br>
格式：TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。<br>
(2)截断日期：</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">8
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#099">1</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-3-18  今天的日期为2011-3-18
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">2</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate, <span style="color:#d14">&#39;mm&#39;</span>)   <span style="color:#000;font-weight:bold">from</span>   dual  <span style="color:#998;font-style:italic">--2011-3-1    返回当月第一天.
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">3</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate,<span style="color:#d14">&#39;yy&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-1-1       返回当年第一天
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">4</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate,<span style="color:#d14">&#39;dd&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-3-18    返回当前年月日
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">5</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate,<span style="color:#d14">&#39;yyyy&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-1-1   返回当年第一天
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">6</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate,<span style="color:#d14">&#39;d&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-3-13 (星期天)返回当前星期的第一天
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">7</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate, <span style="color:#d14">&#39;hh&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual   <span style="color:#998;font-style:italic">--2011-3-18 14:00:00   当前时间为14:41  
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">8</span>.<span style="color:#000;font-weight:bold">select</span> trunc(sysdate, <span style="color:#d14">&#39;mi&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual  <span style="color:#998;font-style:italic">--2011-3-18 14:41:00   TRUNC()函数没有秒的精确
</span></code></pre></td></tr></table>
</div>
</div><h3 id="instr函数的格式--俗称字符查找函数">instr()函数的格式  （俗称：字符查找函数）</h3>
<p>格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)</p>
<p>格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)</p>
<p>解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。</p>
<p>$\color{red}{注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。}$<br>
2、实例</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--格式一
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#099">1</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual; <span style="color:#998;font-style:italic">--返回结果：3    默认第一次出现“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">2</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;lo&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual; <span style="color:#998;font-style:italic">--返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">3</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;wo&#39;</span>) <span style="color:#000;font-weight:bold">from</span> dual; <span style="color:#998;font-style:italic">--返回结果：6    即“wo”同时出现，第一个字母“w”出现的位置
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#998;font-style:italic">--格式二
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">1</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#099">2</span>,<span style="color:#099">2</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：4    也就是说：在&#34;helloworld&#34;的第2(e)号位置开始，查找第二次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">2</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#099">3</span>,<span style="color:#099">2</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：4    也就是说：在&#34;helloworld&#34;的第3(l)号位置开始，查找第二次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">3</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#099">4</span>,<span style="color:#099">2</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：9    也就是说：在&#34;helloworld&#34;的第4(l)号位置开始，查找第二次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">4</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#000;font-weight:bold">-</span><span style="color:#099">1</span>,<span style="color:#099">1</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：9    也就是说：在&#34;helloworld&#34;的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">5</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#000;font-weight:bold">-</span><span style="color:#099">2</span>,<span style="color:#099">2</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：4    也就是说：在&#34;helloworld&#34;的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">6</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#099">2</span>,<span style="color:#099">3</span>) <span style="color:#000;font-weight:bold">from</span> dual;  <span style="color:#998;font-style:italic">--返回结果：9    也就是说：在&#34;helloworld&#34;的第2(e)号位置开始，查找第三次出现的“l”的位置
</span><span style="color:#998;font-style:italic"></span><span style="color:#099">7</span> <span style="color:#000;font-weight:bold">select</span> instr(<span style="color:#d14">&#39;helloworld&#39;</span>,<span style="color:#d14">&#39;l&#39;</span>,<span style="color:#000;font-weight:bold">-</span><span style="color:#099">2</span>,<span style="color:#099">3</span>) <span style="color:#000;font-weight:bold">from</span> dual; <span style="color:#998;font-style:italic">--返回结果：3    也就是说：在&#34;helloworld&#34;的倒数第2(l)号位置开始，往回查找第三
</span></code></pre></td></tr></table>
</div>
</div><p>$\color{red}{注：MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果； 如下所示：}$</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">MySQL<span style="color:#a61717;background-color:#e3d2d2">：</span> <span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> tableName <span style="color:#000;font-weight:bold">where</span> name <span style="color:#000;font-weight:bold">like</span> <span style="color:#d14">&#39;%helloworld%&#39;</span>;
Oracle<span style="color:#a61717;background-color:#e3d2d2">：</span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> tableName <span style="color:#000;font-weight:bold">where</span> instr(name,<span style="color:#d14">&#39;helloworld&#39;</span>)<span style="color:#000;font-weight:bold">&gt;</span><span style="color:#099">0</span>;  <span style="color:#998;font-style:italic">--这两条语句的效果是一样的
</span></code></pre></td></tr></table>
</div>
</div><h2 id="rownum-用法">rownum 用法</h2>
<p>对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n&gt;1的自然数）。<br>
rownum对于大于某值的查询条件<br>
如果想找到从第二行记录以后的记录，当使用rownum&gt;2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum&gt; n(n&gt;1的自然数)这种条件依旧不成立，所以查不到记录。</p>
<p>查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span>(<span style="color:#000;font-weight:bold">select</span> rownum <span style="color:#000;font-weight:bold">no</span> ,id,name <span style="color:#000;font-weight:bold">from</span> student) <span style="color:#000;font-weight:bold">where</span> <span style="color:#000;font-weight:bold">no</span><span style="color:#000;font-weight:bold">&gt;</span><span style="color:#099">2</span>;
</code></pre></td></tr></table>
</div>
</div><h2 id="oracle中的rowid">Oracle中的rowid</h2>
<p>$\color{red}{  ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。}$<br>
通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中。<br>
它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。<br>
所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。<br>
数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的<br>
ROWID：数据库中行的全局唯一地址<br>
对于数据中的每一行，rowid伪列返回行的地址。rowid值主要包含以下信息：</p>
<p>对象的数据对象编号<br>
  该行所在的数据文件中的数据块<br>
  该行中数据块的位置（第一行是0）<br>
  数据行所在的数据文件（第一个文件是1）。该文件编号是相对于表空间。<br>
  通常来说，一个rowid值唯一标识数据中的一行。然而，存储在同一聚簇中不同的表可以有相同的rowid。
$\color{red}{  当需要查询出一条数据并更新或修改查出的数据时，需查询rowid即可修改数据}$</p>
<h2 id="for-update">for update</h2>
<p>for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。<br>
只有当出现如下之一的条件，才会释放共享更新锁：<br>
1、执行提交（COMMIT）语句<br>
2、退出数据库（LOG　OFF）<br>
3、程序停止运行<br>
概念和用法<br>
  通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。<br>
而select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。<br>
由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。<br>
举个例子: 假设有张表user ，里面有 id 和 name 两列，id是主键。<br>
例1: (明确指定主键，并且数据真实存在，row lock)</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">ELECT <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> id<span style="color:#000;font-weight:bold">=</span><span style="color:#099">3</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;

<span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> id<span style="color:#000;font-weight:bold">=</span><span style="color:#099">3</span> <span style="color:#000;font-weight:bold">and</span> name<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;Tom&#39;</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;
</code></pre></td></tr></table>
</div>
</div><p>例2: (明确指定主键，但数据不存在，无lock)</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> id<span style="color:#000;font-weight:bold">=</span><span style="color:#099">0</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;
</code></pre></td></tr></table>
</div>
</div><p>例3: (主键不明确，table lock)</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> id<span style="color:#000;font-weight:bold">&lt;&gt;</span><span style="color:#099">3</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;

<span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> id <span style="color:#000;font-weight:bold">LIKE</span> <span style="color:#d14">&#39;%3%&#39;</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;
</code></pre></td></tr></table>
</div>
</div><p>例4: (无主键，table lock)</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> <span style="color:#000;font-weight:bold">user</span> <span style="color:#000;font-weight:bold">WHERE</span> name<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;Tom&#39;</span> <span style="color:#000;font-weight:bold">FOR</span> <span style="color:#000;font-weight:bold">UPDATE</span>;
</code></pre></td></tr></table>
</div>
</div><p>注意：<br>
$\color{red}{1、FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效。}$</p>
<p>$\color{red}{2、要测试锁定的状况，可以利用MySQL的Command Mode(命令模式) ，开两个视窗来做测试。}$</p>
<p>$\color{red}{3、Myisam 只支持表级锁，InnerDB支持行级锁 添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改。是表级锁时，不管是否查询到记录，都会锁定表。}$</p>
<h3 id="三什么时候需要使用for-update">三、什么时候需要使用for update？</h3>
<p>借助for update语句，我们可以在应用程序的层面手工实现数据加锁保护操作。就是那些需要业务层面数据独占时，可以考虑使用for update。</p>
<p>场景上，比如火车票订票，在屏幕上显示有票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。</p>
<h3 id="四for-update悲观锁">四、for update悲观锁</h3>
<p>悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。</p>
<p>乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。</p>

        </div>

        
<div class="post-archive">
    <ul class="post-copyright">
        <li><strong>原文作者：</strong><a rel="author" href="https://laoer123.gitee.io/laoer123">Jeremy Sze</a></li>
        <li style="word-break:break-all"><strong>原文链接：</strong><a href="https://laoer123.gitee.io/laoer123/post/Oracle%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93/">https://laoer123.gitee.io/laoer123/post/Oracle%E8%AF%AD%E6%B3%95%E6%80%BB%E7%BB%93/</a></li>
        <li><strong>版权声明：</strong>本作品采用<a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/">知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议</a>进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。</li>
    </ul>
</div>
<br/>



        

<div class="post-archive">
    <h2>See Also</h2>
    <ul class="listing">
        
        <li><a href="/laoer123/post/Oracle%E5%9F%BA%E7%A1%80%E6%80%BB%E7%BB%93/">Oracle基础总结</a></li>
        
        <li><a href="/laoer123/post/JdbcTemplate%E6%80%BB%E7%BB%93/">JDBCTemplate总结</a></li>
        
        <li><a href="/laoer123/post/nginx%E5%85%A5%E9%97%A8/">nginx入门</a></li>
        
        <li><a href="/laoer123/post/JWT/">JWT总结</a></li>
        
        <li><a href="/laoer123/post/Rancher_.influxDB_cAdvisor_Grafana/">Rancher_InfluxDB_CAdvisor_Grafana总结</a></li>
        
    </ul>
</div>


        <div class="post-meta meta-tags">
            
            <ul class="clearfix">
                
                <li><a href='https://laoer123.gitee.io/tags/ORACLE'>ORACLE</a></li>
                
            </ul>
            
        </div>
    </article>
    
    <div id="disqus_thread"></div>
<script type="application/javascript">
    var disqus_config = function () {
    
    
    
    };
    (function() {
        if (["localhost", "127.0.0.1"].indexOf(window.location.hostname) != -1) {
            document.getElementById('disqus_thread').innerHTML = 'Disqus comments not available by default when the website is previewed locally.';
            return;
        }
        var d = document, s = d.createElement('script'); s.async = true;
        s.src = '//' + "yourdiscussshortname" + '.disqus.com/embed.js';
        s.setAttribute('data-timestamp', +new Date());
        (d.head || d.body).appendChild(s);
    })();
</script>
<noscript>Please enable JavaScript to view the <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
<a href="https://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a>

    
    
</div>

                </div>

                <div id="secondary">
    <section class="widget">
        <form id="search" action='https://laoer123.gitee.io/laoer123/search/' method="get" accept-charset="utf-8" target="_blank" _lpchecked="1">
      
      <input type="text" name="q" maxlength="20" placeholder="Search">
      <input type="hidden" name="sitesearch" value="https://laoer123.gitee.io/laoer123">
      <button type="submit" class="submit icon-search"></button>
</form>
    </section>
    
    <section class="widget">
        <h3 class="widget-title">最近文章</h3>
<ul class="widget-list">
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/springcloud%E4%B8%8EspringcloudAlibaba%E6%80%BB%E7%BB%93/" title="springcloud与springcloudAlibaba总结">springcloud与springcloudAlibaba总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Seata%E6%80%BB%E7%BB%93/" title="Seata总结">Seata总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/OpenFegin%E6%80%BB%E7%BB%93/" title="OpenFegin总结">OpenFegin总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/SpringCloudGateWay%E6%80%BB%E7%BB%93/" title="SpringCloudGateWay总结">SpringCloudGateWay总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/SpringCloudRibbon/" title="SpringCloudRibbon总结">SpringCloudRibbon总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E9%85%8D%E7%BD%AE%E6%B3%A8%E5%86%8C%E4%B8%8E%E5%8F%91%E7%8E%B0/" title="Nacos配置注册与发现">Nacos配置注册与发现</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E9%85%8D%E7%BD%AE%E7%AE%A1%E7%90%86%E6%BC%94%E7%A4%BA/" title="Nacos配置管理演示">Nacos配置管理演示</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E6%80%BB%E7%BB%93/" title="什么是Nacos">什么是Nacos</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Sentinel%E6%96%B0%E6%89%8B%E6%8C%87%E5%8D%97/" title="Sentinel新手指南">Sentinel新手指南</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Sentinel%E6%8E%A7%E5%88%B6%E5%8F%B0/" title="Sentinel控制台">Sentinel控制台</a>
    </li>
    
</ul>
    </section>

    

    <section class="widget">
        <h3 class="widget-title">分类</h3>
<ul class="widget-list">
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/Linux/">Linux (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/NoSql%E6%95%B0%E6%8D%AE%E5%BA%93/">NoSql数据库 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloud/">SpringCloud (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloudAlibaba/">SpringCloudAlibaba (11)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloudAlibabaSpringCloud/">SpringCloudAlibaba,SpringCloud (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/docker/">docker (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/java%E5%9F%BA%E7%A1%80/">java基础 (10)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/spring/">spring (4)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E4%BA%8B%E5%8A%A1/">事务 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%89%8D%E7%AB%AF%E5%BE%AE%E6%9C%8D%E5%8A%A1/">前端微服务 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%89%8D%E7%AB%AF%E6%A1%86%E6%9E%B6/">前端框架 (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%8C%81%E7%BB%AD%E9%9B%86%E6%88%90/">持续集成 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%90%9C%E7%B4%A2%E5%BC%95%E6%93%8E/">搜索引擎 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%95%B0%E6%8D%AE%E5%B1%82%E6%A1%86%E6%9E%B6/">数据层框架 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库 (5)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%9C%8D%E5%8A%A1%E5%99%A8/">服务器 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%9E%B6%E6%9E%84/">架构 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%B6%88%E6%81%AF%E4%B8%AD%E9%97%B4%E4%BB%B6/">消息中间件 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E7%89%88%E6%9C%AC%E6%8E%A7%E5%88%B6/">版本控制 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E7%BC%93%E5%AD%98/">缓存 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E8%AE%A4%E8%AF%81/">认证 (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E8%BF%90%E7%BB%B4/">运维 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E9%83%A8%E7%BD%B2/">部署 (1)</a></li>
    
</ul>
    </section>

    <section class="widget">
        <h3 class="widget-title">标签</h3>
<div class="tagcloud">
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Dubbo/">Dubbo</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ES6/">ES6</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ElasticSearch/">ElasticSearch</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ElementUI/">ElementUI</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Eureka/">Eureka</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Feign/">Feign</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JDBCTemplate/">JDBCTemplate</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JPA/">JPA</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JWT/">JWT</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Jenkins/">Jenkins</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Linux%E5%9F%BA%E7%A1%80/">Linux基础</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Linux%E9%85%8D%E7%BD%AE%E8%81%94%E7%BD%91/">Linux配置联网</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/MongoDB/">MongoDB</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Nacos/">Nacos</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ORACLE/">ORACLE</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/OpenFegin/">OpenFegin</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/RabbitMQ/">RabbitMQ</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Seata/">Seata</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Sentinel/">Sentinel</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudGateWay/">SpringCloudGateWay</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudRibbon/">SpringCloudRibbon</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudSpringCloudAlibaba/">SpringCloud,SpringCloudAlibaba</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/VUE/">VUE</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/docker/">docker</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/git/">git</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/gogs/">gogs</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/hystrix/">hystrix</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/mysql/">mysql</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/nginx/">nginx</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/nodeJS/">nodeJS</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/npm/">npm</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springCache/">springCache</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springDataJPA/">springDataJPA</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springboot/">springboot</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springcloudbus/">springcloudbus</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springcloudconfig/">springcloudconfig</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springdataredis/">springdataredis</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/zuul/">zuul</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/">分布式事务</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%9B%E5%BB%BA%E6%A8%A1%E5%BC%8F/">创建模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%BE%AE%E6%9C%8D%E5%8A%A1/">微服务</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E6%8E%92%E5%BA%8F/">排序</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E6%9C%8D%E5%8A%A1%E9%95%9C%E5%83%8F%E7%9A%84%E6%9E%84%E5%BB%BA%E4%B8%8E%E9%83%A8%E7%BD%B2/">服务镜像的构建与部署</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E7%BB%93%E6%9E%84%E6%A8%A1%E5%BC%8F/">结构模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%A1%8C%E4%B8%BA%E6%A8%A1%E5%BC%8F/">行为模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%AE%BE%E8%AE%A1%E6%A8%A1%E5%BC%8F/">设计模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%BF%90%E7%BB%B4/">运维</a>
    
</div>
    </section>

    
<section class="widget">
    <h3 class="widget-title">友情链接</h3>
    <ul class="widget-list">
        
        <li>
            <a target="_blank" href="https://www.cnblogs.com/Jeremy95-Sze/" title="Jeremy Sze的博客">Jeremy Sze的博客</a>
        </li>
        
        <li>
            <a target="_blank" href="https://github.com/Jeremy95-Sze" title="Jeremy Sze的github">Jeremy Sze的github</a>
        </li>
        
    </ul>
</section>


    <section class="widget">
        <h3 class="widget-title">其它</h3>
        <ul class="widget-list">
            <li><a href="https://laoer123.gitee.io/laoer123/index.xml">文章 RSS</a></li>
        </ul>
    </section>
</div>
            </div>
        </div>
    </div>
    <footer id="footer">
    <div class="container">
        &copy; 2023 <a href="https://laoer123.gitee.io/laoer123">Jeremy Sze的博客 By Jeremy Sze</a>.
        Powered by <a rel="nofollow noreferer noopener" href="https://gohugo.io" target="_blank">Hugo</a>.
        <a href="https://www.flysnow.org/" target="_blank">Theme</a> based on <a href="https://github.com/flysnow-org/maupassant-hugo" target="_blank">maupassant</a>.
        
    </div>
</footer>


    
    <script src="//cdnjs.cloudflare.com/ajax/libs/webfont/1.6.28/webfontloader.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/snap.svg/0.5.1/snap.svg-min.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.9.1/underscore-min.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/js-sequence-diagrams/1.0.6/sequence-diagram-min.js" crossorigin="anonymous"></script>
        <script>(function () {
            if (!window.Diagram) return;
            const blocks = document.querySelectorAll('pre code.language-sequence');
            for (let i = 0; i < blocks.length; i++) {
                const block = blocks[i];
                
                const rootElement = block.parentNode;
                const container = document.createElement('div');
                const id = `js-sequence-diag-${i}`;
                container.id = id;
                container.className = 'align-center';
                container.setAttribute("style", "overFlow-x:auto");
                rootElement.parentNode.replaceChild(container, rootElement);

                const diagram = Diagram.parse(block.childNodes[0].nodeValue);
                diagram.drawSVG(id, window.sequenceDiagramsOptions
                    ? window.sequenceDiagramsOptions
                    : { theme: 'simple' });
            }
        })();
        </script><script type="text/javascript">
        window.MathJax = {
            tex2jax: {
                inlineMath: [['$', '$']],
                processEscapes: true
                }
            };
    </script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML' async></script>


<a id="rocket" href="#top"></a>
<script type="text/javascript" src='/laoer123/js/totop.js?v=0.0.0' async=""></script>

<script type="application/javascript">
var doNotTrack = false;
if (!doNotTrack) {
	window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)};ga.l=+new Date;
	ga('create', 'GA ID', 'auto');
	
	ga('send', 'pageview');
}
</script>
<script async src='https://www.google-analytics.com/analytics.js'></script>



    <script type="text/javascript" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" async></script>




    <script src='/laoer123/js/douban.js'></script>

</body>

</html>